cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Table7.log, replace

use ./5_postmoney_valuations_failures.dta, clear
sort orgnr time
**identify the actual financing round (disregard the fictive zero/failed rounds)
keep if mv_rounds!=. & mv_rounds!=0
sort orgnr time
bys orgnr: gen totalround=_N
keep orgnr totalround 
duplicates drop
**merge in investor type data
merge 1:m orgnr using ./6_investors.dta
drop _merge
keep if primamount!=0 & primamount!=.
replace totalround=0 if totalround==.
merge m:1 orgnr using ./7_population_firmTVPI.dta, keepusing (sample vcbacked cumequity3)
keep if _merge==3
keep orgnr pyear time investortype primamount primownersh vcbacked sample totalround cumequity3
keep if sample==1

replace primownersh=1 if primownersh>1

foreach t in primamount cumequity3 {
sum `t', d
return list
sum `t' if `t'>r(p99), d
return list
replace `t'=r(p50) if `t'>r(p50) & `t'!=.
replace `t'=`t'/1000000
}

forvalues i = 1(1)8 {
gen prima`i'=primamount if investortype==`i'
gen primo`i'=primownersh if investortype==`i'
replace prima`i'=0 if prima`i'==.
replace primo`i'=0 if primo`i'==.
bys orgnr time: egen primamount`i'=sum(prima`i')
bys orgnr time: egen primownersh`i'=sum(primo`i')
replace primamount`i'=. if primamount`i'==0
replace primownersh`i'=. if primownersh`i'==0
}

keep orgnr time cumequity3 vcbacked totalround primamount* primownersh*
drop primamount primownersh
duplicates drop


outreg2 using ./tables/Table7.xls if totalround<2 & vcbacked==1, replace sum(detail) keep (primamount* primownersh*) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround<2 & vcbacked==0, append sum(detail) keep (primamount* primownersh*) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround>1 & vcbacked==1, append sum(detail) keep (primamount* primownersh*) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround>1 & vcbacked==0, append sum(detail) keep (primamount* primownersh*) eqkeep(N mean)

****
keep orgnr cumequity3 totalround vcbacked
duplicates drop

outreg2 using ./tables/Table7.xls if totalround<2 & vcbacked==1, append sum(detail) keep (cumequity3) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround<2 & vcbacked==0, append sum(detail) keep (cumequity3) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround>1 & vcbacked==1, append sum(detail) keep (cumequity3) eqkeep(N mean)
outreg2 using ./tables/Table7.xls if totalround>1 & vcbacked==0, append sum(detail) keep (cumequity3) eqkeep(N mean)





